package com.yidianyanglao.user.mapper;

import com.yidianyanglao.common.bean.Notice;
import com.yidianyanglao.common.bean.Series;
import com.yidianyanglao.core.bean.MobileCode;
import com.yidianyanglao.user.bean.LuckyTreeBean;
import com.yidianyanglao.user.bean.RecommRecord;
import com.yidianyanglao.user.bean.SeedDetailsBean;
import com.yidianyanglao.user.bean.UserBean;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by wb on 2016/10/28.
 */
@Repository
public interface UserMapper {


    /**
     * 配置权限
     * @param id
     * @return
     */
    @Insert("INSERT INTO role_user_group (user_id,group_id,createtime,create_user)VALUES(#{id},1,NOW(),#{id})")
    int addGroupRole(@Param("id")int id);

    @Insert("INSERT INTO yl_luckytree(name,series_id,user_id,usertype,luckytree,tree_balance,seed_balance,exchange_value,consumption_value,createtime) VALUES(#{name},#{seriesId},#{userId},0,0,#{treeBalance},#{seedBalance},0,0,NOW())")
    int addLuckTree(LuckyTreeBean luckyTreeBean);

    /**
     * 根据账号和密码获取用户id
     * @param account
     * @param password
     * @return
     */
    @Select("SELECT * FROM yl_user WHERE (id = #{account} or mobile = #{account}) AND password = #{password}")
    UserBean getUserId(@Param("account") String account,@Param("password") String password);

    /**
     * 根据id更新token信息
     * @param id
     * @param token
     * @return
     */
    @Update("UPDATE yl_user SET token = #{token} WHERE id = #{id}")
    int updateUserToken(@Param("id")int id,@Param("token")String token);

    /**
     * 根据电话号码和验证码获取一条验证码数据
     * @param mobile
     * @param code
     * @return
     */
    @Select("SELECT id,mobile,code,createdate,enddate,state " +
            "FROM yl_mobilecode " +
            "WHERE " +
            "mobile=#{mobile} AND code=#{code} AND enddate>NOW() AND state=0 " +
            "order by createdate DESC " +
            "limit 0,1")
    MobileCode getMobileCode(@Param("mobile")String mobile, @Param("code") String code);


    /**
     * 根据id修改验证码使用状态
     * @param id
     * @return
     */
    @Update("UPDATE yl_mobilecode " +
            "SET state = 1 " +
            "WHERE id=#{id}")
    int updateMobileCode(@Param("id")int id);

    /**
     * 添加花匠用户
     * @param mobile  电话号码
     * @param password  用户密码
     * @param pid   推荐人id
     * @param pname  推荐人名称，   用户状态   用户类型   注册步骤
     * @return
     */
    @Insert("INSERT INTO yl_user(mobile,password,createtime,pid,pname,pmobile,`status`,usertype,register_status,token,headpic,updatedatetime,up_grade_userid,upup_grade_useruid)" +
            "VALUES(#{mobile},#{password},NOW(),#{pid},#{pname},#{pmobile},2,0,1,#{token},#{headpic},NOW(),0,0)")
    int addUser(@Param("mobile")String mobile,@Param("password")String password,@Param("pid")int pid,@Param("pname")String pname,@Param("pmobile")String pmobile,@Param("token")String token,@Param("headpic")String headpic);

    /**
     * 进入修改第二部，更新部分数据
     * @param userBean
     * @return
     */
    @Update("UPDATE yl_user SET province=#{province},city=#{city},realname=#{realname},certid=#{certid},register_status=2 WHERE id=#{id}")
    int updateUserInfo(UserBean userBean);


    /**
     * 进入修改第三部，更新部分数据
     * @param paypwd,id
     * @return
     */
    @Update("UPDATE yl_user SET paypwd=#{paypwd},`status`=0,register_status=3 WHERE id=#{id}")
    int updatePayPwd(@Param("paypwd")String paypwd,@Param("id")int id);

    /*
     * 通过电话号码获取用户信息
     */
    @Select("SELECT * FROM yl_user WHERE mobile = #{mobile}")
    UserBean getUserInfo(@Param("mobile")String mobile);


    /**
     * 通过token获取用户信息
     * @param token
     * @return
     */
    @Select("SELECT * FROM yl_user WHERE token = #{token}")
    UserBean getUserInfoByToken(@Param("token") String token);

    /**
     * 通过token获取用户信息 事务 数据表锁住方法
     * @param token
     * @return
     */
    @Select("SELECT * FROM yl_user WHERE token = #{token} FOR UPDATE")
    UserBean getUserInfoByTokenForUpdate(@Param("token") String token);

    /**
     * 用户修改密码
     * @param id
     * @param password
     * @return
     */
    @Update("UPDATE yl_user SET password = #{password} WHERE id = #{id}")
    int updateUserPassword(@Param("id")int id,@Param("password")String password);

    /**
     * 用户支付密码
     * @param token
     * @param paypwd
     * @param token
     *
     * @return
     */
    @Update("UPDATE yl_user SET paypwd = #{paypwd} WHERE token = #{token}")
    int updateUserPayPassword(@Param("token")String token,@Param("paypwd")String paypwd);


    /**
     * 更新绑定的手机号码
     * @param mobile
     * @param token
     * @return
     */
    @Update("UPDATE yl_user SET mobile = #{mobile} WHERE token = #{token}")
    int updateUserMobile(@Param("mobile")String mobile,@Param("token") String token);


    /**
     * 更新绑定的手机号码
     * @param headpic
     * @param token
     * @return
     */
    @Update("UPDATE yl_user SET headpic = #{headpic} WHERE token = #{token}")
    int updateUserheadpic(@Param("headpic")String headpic,@Param("token") String token);


    /**
     * 通过账号获取用户信息
     * @param account
     * @return
     */
    @Select("SELECT * FROM yl_user WHERE id = #{account} or mobile = #{account}")
    UserBean getUserByAccount(@Param("account") String account);


    /**
     * 通过账号获取用户信息 事务 数据锁方法
     * @param account
     * @return
     */
    @Select("SELECT * FROM yl_user WHERE id = #{account} or mobile = #{account} FOR UPDATE")
    UserBean getUserByAccountForUpdate(@Param("account") String account);

    /**
     * 更新用户种子情况
     * @param id
     * @param seednum
     * @return
     */
    @Update("UPDATE yl_user SET ordinary_seed = #{seednum},giveseed=#{giveseed} ,version=version+1 WHERE id = #{id} and version=#{version}")
    int UpdateSeed(@Param("id")int id,@Param("seednum")double seednum,@Param("giveseed")double giveseed,@Param("ordinarySeedNum")double ordinarySeedNum,@Param("version")int version);


    /**
     * 转赠更新种子明细
     * @param seedDetailsBean
     * @return
     */
    @Insert("INSERT INTO yl_seed_details (seedtype,name,user_id,change_seed,remaining_seed,turnpeople_id,turnpeople_name,processing_fee_value,tax_point_value,processing_fee,tax_point,obtainpeople_id,obtainpeople_name,usertype)" +
            "VALUES(#{seedtype},#{name},#{userId},#{changeSeed},#{remainingSeed},#{turnpeopleId},#{turnpeopleName},#{processingFeeValue},#{taxPointValue},#{processingFee},#{taxPoint},#{obtainpeopleId},#{obtainpeopleName},#{usertype})")
    int addSeedDetailZz(SeedDetailsBean seedDetailsBean);

    /**
     * 添加一条推荐记录
     * @param recommRecord
     * @return
     */
    @Insert("INSERT INTO yl_recommend_record(fromuser_id,touser_id,from_user_name,to_user_name,from_user_usertype,to_user_usertype,createdate,from_user_seedreward,to_user_seedreward)" +
            "VALUES(#{fromuserId},#{touserId},#{fromUserName},#{toUserName},#{fromUserUsertype},#{toUserUsertype},NOW(),#{fromUserSeedreward},#{toUserSeedreward})")
    int addRecomm(RecommRecord recommRecord);

    /**
     * 根据类型获取消息列表
     */
    @Select("SELECT * FROM yl_notice WHERE user_id = #{userid} AND noticetype = #{noticetype} ORDER BY createdate DESC LIMIT #{pagenum},10")
    List<Notice> getNoticeByType(@Param("userid")int userid,@Param("noticetype")int noticetype,@Param("pagenum") int pagenum);

    /**
     * 获取首页弹窗的消息
     */
    @Select("SELECT * FROM yl_notice WHERE noticetype < 2 ORDER BY createdate DESC LIMIT 1")
    List<Notice> noticeForAlert();

    /**
     * 更新状态为已读
     * @param id
     * @return
     */
    @Update("UPDATE yl_notice SET readstate = 1 WHERE readstate = 0 AND id = #{id} AND noticetype>1")
    int updateToRead(@Param("id")int id);

    /**
     * 消息首页获取每个分类第一条
     * @param userid
     * @return
     */
    @Select("SELECT * FROM (SELECT * FROM yl_notice WHERE user_id = #{userid} ORDER BY  createdate DESC) a GROUP BY a.noticetype")
    List<Notice> getNoticeListForindex(@Param("userid")int userid);



    /**
     * 测试方法，测试方法，测试方法
     * @param name
     * @return
     */
    @Insert("INSERT INTO test(name) values(#{name})")
    int addUserTest(String name);

    @Update("UPDATE test set name=#{name}")
    int updateUserTest(String name);

    @Select("select name from test")
    List<String> getList();

    /**
     * 获取今日激励的数据，从种子明细里面获取今日普通种子的累积和,追加系列
     */
    @Select("SELECT SUM(luckytreenum) as luckytreenum,SUM(change_seed) as change_seed FROM yl_seed_details WHERE `name`='激励' AND seedtype = '普通' AND user_id = #{id} AND createdate > #{today} AND series_id = #{seriesId}")
    //@Select("SELECT SUM(luckytreenum) as luckytreenum,SUM(change_seed) as change_seed FROM yl_seed_details WHERE `name`='激励' AND seedtype = '普通' AND user_id = #{id} AND series_id = #{seriesId} GROUP BY createdate ORDER BY createdate DESC LIMIT 1")
    Map<String,Object> getUserYesterdayExcitation(@Param("id")int id, @Param("seriesId")int seriesId, @Param("today")Date today);

    /**
     * 给用户发注册种子
     * @param id
     * @return
     */
    @Update("UPDATE yl_user SET registered_seed = registered_seed+10,registered_seed_num = registered_seed_num+10 WHERE id = #{id}")
    int updateRegisteredSeed(@Param("id")int id);


    /**
     * 更新用户推荐用户的电话号码
     * @param pid
     * @param pmobile
     * @return
     */
    @Update("UPDATE yl_user SET pmobile=#{mobile} WHERE pid = #{pid}")
    int updateUserRecommendMobile(@Param("pid")int pid,@Param("mobile")String pmobile);

    /**
     * 更新用户变更手机号后，同步订单
     * @param id
     * @param mobile
     * @return
     */
    @Update("UPDATE yl_order SET consumption_mobile=#{mobile} WHERE consumption_id=#{id}")
    int updateOrderUsermobile(@Param("id")int id,@Param("mobile")String mobile);

    /**
     * 查询系列数据
     * @return
     */
    @Select("SELECT * FROM yl_series")
    List<Series> getSeries();


    /**
     * 修改真实姓名和身份证号
     * @param user
     * @return
     */
    @Update("UPDATE yl_user SET realname=#{realname},certid=#{certid} where id=#{id}")
    int updateUserBaseInfo(UserBean user);



}
